Writing Stored Calculations
When writing a Member Formula or a Business Rule for a Stored Calculation, the new calculated numbers are being determined to store for that Cube, Entity, Parent, Cons, Scenario, and Time combination (i.e., a Data Unit).
Return is never seen in a Member Formula for Formula Pass. Instead of being returned, many numbers are being calculated and stored. When running a Calculation, Translation, or Consolidation, the Member Formula calls for an entire Data Unit. It does not tell with which Account, Flow, or User Defined the numbers are being saved, this is the responsibility of the user. Initially, this may be confusing because Member Formulas are often written in an account’s Formula property, and administrators believe it will only allow that specific Member Formula to write to that specific account. However, putting a Member Formula in an account’s Formula property is only for organizational purposes. When it calls that formula, it is currently calculating a Data Unit and it will initialize the api with only the Data Unit Dimensions.
Basic Stored Formula Examples
The formula examples in this section demonstrate how to calculate basic stored values driven by formulas consisting of OneStream Member Script expressions.
Copy Data from another Account
The following formula would be implemented as a Member Formula on the Sales1 account. It is executed as part of the DUCS during the Formula Pass that was specified in the account’s Formula Type setting.
api.Data.Calculate("A#Sales1 = A#Sales2”)
Stored Formula passes use Data Buffer math, not the Data Cell math that occurs for the single cell Dynamic Calc formulas. Stored Formulas are multi-Dimensional. For example, the formula is executed for an entire Data Unit (e.g., Location1 Entity, USD Consolidation Member, Actual Scenario, January 2013 time period). That Data Unit is a portion of a Cube where the UD1 Dimension could contain 1,000 products to keep track of sales by product. Therefore, the data for the Sales2 account could contain a separate number for every Product (i.e., UD1), or if the Location1 Entity only sells some of the products, there might be 200 numbers for Sales2 and the other 800 products for Sales2 are NoData. That set of 200 numbers is called a Data Buffer. Data Buffers can get much larger and more complicated when multiple Dimensions are used for detailed analyses. However, since the same concepts still apply, it is easier to think about a smaller set of Dimensions as in this example.
The formula “A#Sales1 = A#Sales2” is equivalent to saying, “Take the 200 numbers stored in the Sales2 Data Buffer and copy them to a new Data Buffer, but change the account to Sales1, and then store the new Sales1 Data Buffer in the database.” That one-line formula calculated and stored an additional 200 numbers that did not exist before the formula was executed.
The formula below reads the Data Buffer for the Sales2 account (200 numbers) and then adds 50.0 to each of those 200 numbers to create a new Data Buffer that also contains 200 numbers. The account for each of the 200 numbers in the new Data Buffer is changed to Sales1 and it is then stored in the database.
api.Data.Calculate("A#Sales1 = A#Sales2 + 50.0”)
The newly modified formula below uses three accounts. That formula reads the Data Buffer for the Sales2 account (200 numbers) and then reads the Data Buffer for the Sales3 account. For example, the Sales3 account contains 100 numbers broken out by product in UD1, and 25 of those 100 numbers use the same UD1 Members as some of the numbers from Sales2. The other 75 Sales3 numbers are for other products not used by the Sales2 account. OneStream automatically combines the numbers from Sales2 and Sales3 and adds the Sales2 and Sales3 numbers that share a common intersection and also adds the additional non-common intersections. The result is a new Data Buffer containing 275 numbers stored in the database for the Sales1 account.
api.Data.Calculate("A#Sales1 = A#Sales2 + A#Sales3”)
Copy Data from Another Scenario
The following formula would be implemented as a Member Formula on the Forecast Scenario. It copies all the data from the Actual Scenario and stores the results in the Forecast Scenario. The If statement in this example causes the data to be copied only for Base-Level Entities and each Entity’s local currency. This is because the example application wants the numbers for Parent Entities and foreign currencies to be determined using OneStream’s Consolidation and Translation algorithms, not by copying directly from another Scenario. Limit the formula to only copy data for certain Dimension Members by adding specific Members in the formula’s Member Script. For example, if the Forecast Scenario needs to copy only the Import Members, add O#Import to both sides of the equation. If the Forecast Scenario needs to start with the sum of the Actual Scenario’s Import plus Forms plus AdjInput data and copy that into the Forecast Scenario’s O#Import Member, then use “S#Forecast:O#Import = S#Actual:O#Top”. When writing Stored Formulas, any Data Unit Dimension not explicitly specified uses the Dimension Member for the Data Unit currently being calculated. For any Account, Flow, Origin, IC, or User Defined Dimensions not explicitly specified, OneStream will use #All which is the syntax that represents all existing data for that Dimension.
If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyForEntity())) Then
api.Data.Calculate("S#Forecast = S#Actual")
End If
To copy data from another Cube or Scenario that uses different Dimensionality, an example of the Scenario formula would be as follows:
'Convert dimensionality
Dim destinationInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("")
Dim sourceDataBuffer As DataBuffer = api.Data.GetDataBuffer(DataApiScriptMethodType.Calculate,
"Cb#AnotherCube:S#AnotherScenario",destinationInfo)
Dim convertedDataBuffer As DataBuffer = api.Data.ConvertDataBufferExtendedMembers("AnotherCube",
"AnotherScenario", sourceDataBuffer)
api.Data.SetDataBuffer(convertedDataBuffer, destinationInfo)
To drill down on this formula, use the following example in the Scenario’s Formula for Calculation Drill Down setting:
If api.Pov.Cube.Name.XFEqualsIgnoreCase("TheDestCube") Then
Dim result As New DrillDownFormulaResult()
result.Explanation = "Pseudo-formula: Cb#TheDestCube:S#TheDestScenario=Cb#AnotherCube:
S#AnotherScenario”
result.SourceDataCells.Add("Cb#AnotherCube:S#AnotherScenario")
Return result
End If
Return Nothing
Out-Of-Balance
The following formula would be implemented as a Member Formula on the Balance account. It stores the difference of two other accounts.
If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyForEntity())) Then
api.Data.Calculate("A#Balance = A#2899 - A#5999")
End If
CTA Account Formula Examples
This is essentially the same formula as Out-Of-Balance, but only runs on translated Data Units.
If ((Not api.Entity.HasChildren()) And (api.Cons.IsForeignCurrencyForEntity())) Then
api.Data.Calculate("A#CTA = A#2899 - A#5999")
End if
Reading a Specific Data Cell in a Stored Formula
Stored Formulas are executed for an entire Data Unit, so unlike Dynamic Calc formulas, there is no context about a specific View, Account, Flow, Origin, IC, or User Defined Member within the Data Unit. Therefore, to read the value for a Data Cell using api.Data.GetDataCell, a Member needs to be explicitly specified for all non-Data Unit Dimensions. This is different than Dynamic Calc Member Formulas where the default setting for every Dimension Member comes from the Data Cell currently being displayed.
Therefore, the following is incorrect when trying to read a specific Data Cell inside a Stored Formula:
Dim objDataCell As DataCell = api.Data.GetDataCell("A#Cash")
Instead, all non-Data Unit Dimensions need to be specified. If one or more of those Dimensions needs to be based on the other data stored in the Data Unit, then use Eval (for details on Eval see Advanced Stored Formulas using Eval below). Otherwise, the formula will look like this:
Dim objDataCell As DataCell = api.Data.GetDataCell("V#YTD:A#Cash:F#None:O#Import:I#None
:U1#None:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None")
The syntax above is accurate but creating that long string for many Dimensions is tedious and error prone especially when using functions to determine what the Member names should be, and then concatenating multiple strings. Instead it is recommended to use the MemberscriptBuilder class when creating Member Scripts.
Dim MemberscriptBldr = New MemberscriptBuilder("V#YTD:A#Cash")
MemberscriptBldr.SetFlow("None").SetOrigin("Import").SetIC("None").SetAllUDsToNone()
Dim Memberscript As String = MemberscriptBldr.GetMemberscript()
Dim objDataCell As DataCell = api.Data.GetDataCell(Memberscript)
Advanced Stored Formulas using Eval
As described in the examples above, OneStream’s Data Buffer math is extremely powerful and can process hundreds or thousands of numbers with just one simple equation. Without Data Buffer math or an equivalent scripting capability, a large multi-Dimensional financial application would not be feasible because every intersection would need to be considered separately. There is a consequence when processing data using Data Buffers instead of individual Data Cells. Additional capabilities are needed when wanting to perform math differently based on the individual data cell amounts.
Fortunately, OneStream accommodates that pattern using Eval. When implementing api.Data.Calculate functions, Eval has an advanced capability that provides the ability to get at the individual Data Cells in any Data Unit created while processing an api.Data.Calculate script. It even allows Eval() to be wrapped around a subset of the formula’s math in order to evaluate the Data Buffer that was just created by running that math.
As an example for Eval, start with this formula:
api.Data.Calculate("A#Sales1 = A#Sales2”)
The Sales2 numbers need to be copied to Sales1 for “green” Products. In this fictitious example, there is a special tax situation for green products and the sales numbers for those products need to be isolated into the special Sales1 account. The application uses the UD1 Member’s Text1 property to keep track of which products are green.
The first thought might be to do something like the following (incorrect):
Dim ud1Id As Integer = api.Pov.UD1.MemberId
Dim text1 As String = api.UD1.Text(ud1Id, 1)
If (text1.Equals("green", StringComparison.InvariantCultureIgnoreCase)) Then
api.Data.Calculate("A#Sales1 = A#Sales2")
End If
However, this would not work because Stored Formulas are executed for an entire Data Unit. A Data Unit represents all data for a Cube, Scenario, Entity, Parent, Cons, and Time Member. Since there is no single product (i.e., UD1 Member) for the Data Unit currently being calculated, the first line above does not make sense. A Data Unit cannot be asked what the UD1 MemberId is because a Data Unit has data for multiple UD1 Members (200 different products in the Sales2 example).
The solution is using Eval to evaluate the individual Data Cells in a Data Buffer. Put the Eval keyword around any portion of the api.Data.Calculate function including math statements. After OneStream reads or calculates the DataBuffer defined within the Eval statement, it executes the Eval function to give the opportunity to filter the list of Data Cells in the Data Buffer, or to completely change the list of Data Cells in the Data Buffer. After the Eval function is completed, OneStream uses the modified Data Buffer to perform the remaining part of the api.Data.Calculate function.
The example formula needs to be modified by adding the Eval keyword around the A#Sales2 Data Buffer, a helper function, typically the name OnEvalDataBuffer, needs to be implemented allowing an inspection, filter, and/or change to the Data Cells in the Data Buffer. The helper function Loops over each of the Sales2 Data Cells (200 in this example). If the Data Cell’s UD1 Text1 setting says green, add that Data Cell to a new list of result cells. Otherwise, ignore the Data Cell causing it to be skipped. The result is a new modified Data Buffer containing only the Data Cells for green products (i.e., fewer than 200 Data Cells).
api.Data.Calculate("A#Sales1 = Eval(A#Sales2)", AddressOf OnEvalDataBuffer)
Private Sub OnEvalDataBuffer(ByVal api As FinanceRulesApi, ByVal evalName As String, _
ByVal eventArgs As EvalDataBufferEventArgs)
'Filter to list of Sales2 source numbers to only include numbers
'for "green" Products using each dataCell's UD1 Text1 setting.
'The final list of resultCells is what will be assigned to Sales1 by api.Data.Calculate.
Dim resultCells As New Dictionary(Of DataBufferCellPk, DataBufferCell)
For Each sourceCell As DataBufferCell In eventArgs.DataBuffer1.DataBufferCells.Values
If (Not sourceCell.CellStatus.IsNoData) Then
Dim ud1Id As Integer = sourceCell.DataBufferCellPk.UD1Id
Dim text1 As String = api.UD1.Text(ud1Id, 1)
If (text1.Equals("green", StringComparison.InvariantCultureIgnoreCase)) Then
'Add this dataCell to the new list.
resultCells(sourceCell.DataBufferCellPk) = sourceCell
End If
End If
Next
'Assign the new list of DataCells to the result.
eventArgs.DataBufferResult.DataBufferCells = resultCells
End Sub
NOTE: If using Eval for NoData and ZeroCells, refer to the Remove Functions in Formulas section for alternative performance enhancing solutions.
Advanced Stored Formulas using GetDataBuffer and SetDataBuffer
Most of the advanced stored formulas that need to process multiple Data Cells should use Eval. Eval allows the user to get at the individual Data Cells in any Data Unit while processing the Member Script in an api.Data.Calculate function. However, in some rare occasions, an appropriate Member Script may not be defined for the api.Data.Calculate function because multiple Data Cells that seem completely unrelated to each other are being processed and none of the Dimension Members are constant.
For those rare situations, use the GetDataBuffer and SetDataBuffer functions directly. GetDataBuffer and SetDataBuffer are more fundamental then Eval. They are part of the internal implementation of the Eval functionality. They allow the user to read some numbers using a Member Script, process or modify each cell in the result, and then save the changes.
In the following example, the UD2 and UD3 Dimensions are being used to analyze data based on each UD1 Member’s default settings for UD2 and UD3. All data is initially loaded to the U2#Input:U3#Input Members, but that loaded data needs to be copied to the U2#DefaultUD1:U3#DefaultUD1 Members. GetDataBuffer needs to be used in order to read the loaded data because the destination UD2 and UD3 Members can be different for every Data Cell based on its UD1 Member’s settings. Loop over each Data Cell and use the UD1 Member to get its default UD2 and default UD3 settings. Then, change the UD2 and UD3 Member IDs for the Data Cell in the Data Buffer. Finally, after Looping, call SetDataBuffer to save the new numbers.
When using api.Data.Calculate functions with or without Eval, it is important to know to which Member a formula is being attached. For example, if the formula starts with api.Data.Calculate(“A#Sales1 = …”), put the formula in the Sales1 account Member’s Formula setting. However, the formula in this example is not writing to a specific Member. Every Data Cell being saved is possibly written to a different UD2 and UD3 Member.
Technically speaking, the formula can be put in any Member’s Formula property even a seemingly unrelated Member. If the Formula Pass is set correctly, the formula executes before any other dependent formulas. Assigning stored formulas to Members is for organizational purposes only. The Member does not restrict what the formula can do. However, if a formula is attached to an unrelated Member, it will make the application difficult to maintain and understand. Therefore, decide to attach a formula like this to the Scenario’s Member which means this formula needs to be processed before most other formula passes. If there are numerous Scenarios, this formula should be put in a Business Rule file, and the Business Rule file should be added to the Cube(s). This can be done under
Application Tab|Cube|Cubes.
See the completed formula using GetDataBuffer and SetDataBuffer below:
'Copy all "U2#Input:U3#Input" numbers for this dataUnit to the corresponding UD1 default Members for
UD2 and UD3.
Dim destinationInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("")
Dim sourceDataBuffer As DataBuffer = api.Data.GetDataBuffer(DataApiScriptMethodType.Calculate, _
"U2#Input:U3#Input", destinationInfo)
If Not sourceDataBuffer Is Nothing Then
Dim resultDataBuffer As DataBuffer = New DataBuffer()
For Each cell As DataBufferCell In sourceDataBuffer.DataBufferCells.Values
If (Not cell.CellStatus.IsNoData) Then
Dim ud1Id As Integer = cell.DataBufferCellPk.UD1Id
cell.DataBufferCellPk.UD2Id = api.UD1.GetDefaultUDMemberId(ud1Id, DimType.UD2.Id)
cell.DataBufferCellPk.UD3Id = api.UD1.GetDefaultUDMemberId(ud1Id, DimType.UD3.Id)
resultDataBuffer.SetCell(api.DbConnApp.SI, cell)
End If
Next
api.Data.SetDataBuffer(resultDataBuffer, destinationInfo)
End If
Comparing Two DataBuffers Using Eval2
Eval2 is the same as Eval except two Members Scripts are specified to define two DataBuffers. When finished, the OnEvalDataBuffer function can compare all the numbers in the two DataBuffers. The example provided evaluates two separate Flow Members to see if they contain the same value.
'Use Eval2 to compare the numbers in 2 DataBuffers and store a value in
F#USDOverride_Check for each pair of numbers that don't match.
If ((api.Cons.IsLocalCurrencyForEntity()) And (Not api.Entity.HasChildren())) Then
api.Data.Calculate("V#YTD:F#USDOverride_Check:O#Forms = Eval2(V#YTD:F#Local_Change_Validation:O#Top,
V#YTD:F#USDOverride_Change_Validation:O#Top)", AddressOf OnEvalDataBuffer) End If Private Sub OnEvalDataBuffer(ByVal api As FinanceRulesApi, ByVal evalName As String,
ByVal eventArgs As EvalDataBufferEventArgs) 'This function compares the numbers in 2 DataBuffers and returns a new DataBuffer
that has a value for each pair of numbers that don't match. eventArgs.DataBufferResult.DataBufferCells.Clear() If Not eventArgs.DataBuffer1 Is Nothing And Not eventArgs.DataBuffer2
Is Nothing Then 'For each cell in DataBuffer1, try to find a number for the same intersection in
DataBuffer2. For Each cell1 As DataBufferCell In eventArgs.DataBuffer1.DataBufferCells.Values If (Not cell1.CellStatus.IsNoData) Then Dim cell2 As DataBufferCell = eventArgs.DataBuffer2.GetCell(api.SI, cell1.DataBufferCellPk) If Not cell2 Is Nothing Then If (cell1.CellAmount <> cell2.CellAmount) Then ‘Since the numbers don't match, add a cell to the result
DataBuffer. Dim resultCell As New DataBufferCell(cell1) resultCell.CellAmount = 1.0 eventArgs.DataBufferResult.SetCell(api.SI, resultCell,
False) End If Else 'A number exists in the 1st DataBuffer, but it doesn't exist in
the 2nd DataBuffer. 'Therefore, add a cell to the result DataBuffer. Dim resultCell As New DataBufferCell(cell1) resultCell.CellAmount = 1.0 eventArgs.DataBufferResult.SetCell(api.SI, resultCell, False) End If End If Next 'Now, for each cell in DataBuffer2, try to find a number for the same intersection in
DataBuffer1
(the opposite of the loop above). 'Create a cell in the result DataBuffer for each DataBuffer2 cell that doesn't exist
in DataBuffer1. For Each cell2 As DataBufferCell In eventArgs.DataBuffer2.DataBufferCells.Values If (Not cell2.CellStatus.IsNoData) Then Dim cell1 As DataBufferCell = eventArgs.DataBuffer1.GetCell(api.SI, cell2.DataBufferCellPk) If cell1 is Nothing Then 'A number exists in the 2nd DataBuffer, but it doesn't
exist in the 1st DataBuffer. 'Therefore, add a cell to the result DataBuffer. Dim resultCell As New DataBufferCell(cell2) resultCell.CellAmount = 1.0 eventArgs.DataBufferResult.SetCell(api.SI, resultCell, False) End If End If Next End If End Sub
Referencing a Business Rule from a Member Formula or Business Rule
Finance Business Rules from Member Formulas or other Business Rules can be called. This is helpful when the same code must be copied to multiple Member Formulas and instead of using the same complicated code, a Public Function with two lines of code written in a Business Rule can be called.
First, create a new Finance Business Rule (in this example, a Finance Business Rule called SharedFinanceFunctions was created) and then set the Contains Global Functions for Formulas property to True. If the Business Rule is only being used to hold Shared Functions, delete most of the content in the Main function. However, a Main function is still needed even if it is empty.
Next, create a Public Function or Sub in the Business Rule. See below for an example. If any edits to the Business Rule impact Calculation Status, assign the Shared Business Rule to the Cube under
Application Tab|Cube|Cubes. This is recommended.
Use the Business Rule in a Member Formula by creating an instance of the Business Rule and assigning it to a variable. Then, any of the Business Rule’s Public Functions or Sub can be called.
Sample Member Formula Code:
Dim sharedFinanceBR As New OneStream.BusinessRule.Finance.SharedFinanceFunctions.MainClass
Dim myResult As String = sharedFinanceBR.Test(si, api, args)
Sample Business Rule:
Imports System
Imports System.Data
Imports System.Data.Common
Imports System.IO
Imports System.Collections.Generic
Imports System.Globalization Imports System.Linq Imports Microsoft.VisualBasic Imports System.Windows.Forms Imports OneStream.Shared.Common Imports OneStream.Shared.Wcf Imports OneStream.Shared.Engine Imports OneStream.Shared.Database Imports OneStream.Stage.Engine Imports OneStream.Stage.Database Imports OneStream.Finance.Engine Imports OneStream.Finance.Database
Namespace OneStream.BusinessRule.Finance.SharedFinanceFunctions Public Class MainClass Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As Object Try Return Nothing Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function Public Function Test(ByVal si As SessionInfo, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As String Try Return "This is the result of my Test function!" Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function End Class End Namespace
Conditionally Apply Formulas to Entities and Consolidation Members
Conditionally apply formulas to run only when needed. For example, calculate headcount only at Base Entities and Local currency and the consolidation engine will do the rest.
If (api.cons.IsLocalCurrencyForEntity And (Not api.entity.HasChildren)) Then
api.data.calculate("A#10999=A#25999-A#25986")
End If
Focus Consolidation to Calculate Only When Needed
There are up to seven calculation operations per Entity in the Consolidation process. Add Conditional Statements to formulas to limit which Consolidation calculation processes will run for a particular formula.
Base Entities Only
If Not api.entity.HasChildren Then
Local Currency Only
If Api.Cons.IsLocalCurrencyforEntity Then
Translated Currency Only
If api.Cons.IsForeignCurrencyForEntity Then
Parent-Child Relationships
If api.Cons.IsForeignCurrencyForEntity Then returns True if the current calculated Consolidation Member also depends on the Parent Entity (i.e. OwnerPreAdj, Share, Elimination, OwnerPostAdj, Top). If there are two different Parent Entities for the same Entity, then there are two different sets of numbers stored for those Consolidation Members.
At Specific Level of Consolidation
If (Not api.Entity.HasChildren()) And (api.Pov.Cons.Name.XFEqualsIgnoreCase("Elimination")) Then would run if the Entity is a parent and also if the member of the Consolidation dimension being processed in the Data Unit is Elimination. Note that this is the preferred function to use rather than the formerly supported api.Pov.Cons.ScriptName.
Formulas for Calculation Drill Down
To drill down on calculated Members, a formula must be entered in the Formula for Calculation Drill Down property. This allows drilling to occur on calculated Account, Flow, User Defined or Scenario Members.
Drill down can occur on data cells copied from one Scenario to another via formula or Data Management Sequence. Before displaying the drill results, every cell’s Formula for Calculation DrillDown Scenario Property is executed. The result determines whether the Scenario Member will appear as drillable or not. Therefore, use If Statements in the formula to narrow in on the cell’s Storage Type and/or the POV Members associated with the data copy, so cells do not appear drillable when they are not. The example below copies data from the Actual Scenario Type to Budget:
Dim result As New DrillDownFormulaResult() If args.DrillDownArgs.RequestedDataCell.CellStatus.StorageType = DataCellStorageType.Calculation Then ‘Use this to drill down to data that was copied using a Scenario Formula. result.Explanation = “Formula Definition: Actual = Budget” result.SourceDataCells.Add(“Cb#Houston:E#Houston:S#Actual”) Else If args.DrillDownArgs.RequestedDataCell.CellStatus.StorageType = DataCellStorageType.Input Then ‘Use this to drill down to data that was copied using Data Management. ‘result.Explanation = “Data Management Defintion: Actual = Budget” ‘result.SourceDataCells.Add(“Cb#Houston:E#Houston:S#Actual”) End If Return Result
Global Entity for Driver Storage
A Global Entity can be created to store information that is unrelated to the company’s data such as switches, drivers, or values. To do this within the application, create the Entity and set the Is Consolidated property to False. The Consolidation process will then skip this Entity allowing companies to use this for grouping purposes only.